from typing import List

from sqlalchemy import insert, select, text, update, delete, and_
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import joinedload

from app.database.functions.user import get_user
from app.database.models import Account, IdAccount, User


async def add_account(session: AsyncSession, data: list) -> None:

    user = await get_user(session, data[0])
    account = Account(
        user=user,
        area=data[1],
        login=data[2],
        password=data[3],
        email=data[4],
        passMail=data[5],
        useragent=data[6],
    )

    session.add(account)

    await session.commit()


async def add_account_id(
    session: AsyncSession, telegram_id: int, account_id: int
) -> None:

    await session.execute(
        insert(IdAccount).values(
            customer=select(User.id)
            .where(User.telegram_id == telegram_id)
            .scalar_subquery(),
            ID_akk=account_id,
        )
    )
    await session.commit()


async def get_user_accounts(session: AsyncSession, user_id: int) -> List[Account]:
    q = select(Account).outerjoin(Account.user).where(User.telegram_id == user_id)

    res = await session.execute(q)

    return res.scalars().all()


async def delete_user_accounts(session: AsyncSession, user_id: int) -> None:

    user = await get_user(session, user_id, joined=True)

    q = delete(Account).where(
        and_(
            Account.user == user,
            Account.status.not_in(("zagruzil doki", "zagruzil selfy")),
            Account.result_mes,
        )
    )

    await session.execute(q)

    await session.commit()


async def get_selfie_accounts(session: AsyncSession) -> List[Account]:

    q = (
        select(Account)
        .where(Account.status.in_(("zagruzil doki", "zagruzil selfy")))
        .options(joinedload(Account.user))
    )

    res = await session.execute(q)

    return res.scalars().all()


async def get_account_by_login(session: AsyncSession, account_login: str) -> Account:

    q = select(Account).where(Account.login == account_login)

    res = await session.execute(q)

    return res.scalar()


async def update_account(session: AsyncSession, result: str, account_id: int) -> None:

    q = (
        update(Account)
        .where(Account.ID_akk == account_id)
        .values(result_mes=text(result), status_execute=text("'done'"))
    )

    await session.execute(q)

    await session.commit()


async def update_id_account(
    session: AsyncSession, result: str, account_id: int
) -> None:

    q = (
        update(IdAccount)
        .where(IdAccount.ID_akk == account_id)
        .values(result_mes=text(result), status=text("'done'"))
    )

    await session.execute(q)

    await session.commit()
